package veterinaria.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;


import veterinaria.excepcion.DAOExcepcion;
import veterinaria.modelo.Cliente;
import veterinaria.util.ConexionDB;

public class ClienteDAO extends BaseDAO {
        
        public Cliente insertar(Cliente vo) throws DAOExcepcion {
        	
        	if (buscarPorIdCliente(vo.getIdCliente()).size()>0){
            	System.out.println("El DNI " + vo.getIdCliente() + " ya existe");
            	vo=null;
            }else{
                
                String query = "insert into cliente(idCliente,Nombre, ApellidoMat, ApellidoPat,email,direccion,foto,telf1,telf2,nota) values (?,?,?,?,?,?,?,?,?,?)";
                Connection con = null;
                PreparedStatement stmt = null;
                ResultSet rs = null;
                try {
                        con = ConexionDB.obtenerConexion();
                        stmt = con.prepareStatement(query);
                        stmt.setString(1, vo.getIdCliente());
                        stmt.setString(2, vo.getNombre());
                        stmt.setString(3, vo.getApellidoMat());
                        stmt.setString(4, vo.getApellidoPat());
                        stmt.setString(5, vo.getEmail());
                        stmt.setString(6, vo.getDireccion());
                        stmt.setString(7, vo.getFoto());
                        stmt.setString(8, vo.getTelf1());
                        stmt.setString(9, vo.getTelf2());
                        stmt.setString(10, vo.getNota());
                        int i = stmt.executeUpdate();
                        if (i != 1) {
                                throw new SQLException("No se pudo insertar");
                        }
                } catch (SQLException e) {
                        System.err.println(e.getMessage());
                        throw new DAOExcepcion(e.getMessage());
                } finally {
                        this.cerrarResultSet(rs);
                        this.cerrarStatement(stmt);
                        this.cerrarConexion(con);
                }
                System.out.println("El usuario se registro correctamente");
            }
                return vo;
        
        }

        public Cliente actualizar(Cliente vo) throws DAOExcepcion {
    		String query = "update cliente set nombre=?,ApellidoMat=?,ApellidoPat=?,Email=?,Direccion=?,foto=?,Telf1=?,Telf2=?,nota=? where idCliente=?";
    		Connection con = null;
    		PreparedStatement stmt = null;
    		try {
    			con = ConexionDB.obtenerConexion();
    			stmt = con.prepareStatement(query);
    			stmt.setString(1, vo.getNombre());
                stmt.setString(2, vo.getApellidoMat());
                stmt.setString(3, vo.getApellidoPat());
                stmt.setString(4, vo.getEmail());
                stmt.setString(5, vo.getDireccion());
                stmt.setString(6, vo.getFoto());
                stmt.setString(7, vo.getTelf1());
                stmt.setString(8, vo.getTelf2());
                stmt.setString(9, vo.getNota());
                stmt.setString(10, vo.getIdCliente());
    			int i = stmt.executeUpdate();
    			if (i != 1) {
    				throw new SQLException("No se pudo actualizar");
    			}
    		} catch (SQLException e) {
    			System.err.println(e.getMessage());
    			throw new DAOExcepcion(e.getMessage());
    		} finally {
    			this.cerrarStatement(stmt);
    			this.cerrarConexion(con);
    		}
    		return vo;
    	}
        
        public void eliminar(String idCliente) throws DAOExcepcion {
    		String query = "delete from cliente WHERE idCliente=?";
    		Connection con = null;
    		PreparedStatement stmt = null;
    		try {
    			con = ConexionDB.obtenerConexion();
    			stmt = con.prepareStatement(query);
    			stmt.setString(1, idCliente);
    			int i = stmt.executeUpdate();
    			if (i != 1) {
    				throw new SQLException("No se pudo eliminar");
    			}
    		} catch (SQLException e) {
    			System.err.println(e.getMessage());
    			throw new DAOExcepcion(e.getMessage());
    		} finally {
    			this.cerrarStatement(stmt);
    			this.cerrarConexion(con);
    		}
    	}
        
        public Cliente obtener(String idCliente) throws DAOExcepcion {
    		Cliente vo = new Cliente();
    		Connection con = null;
    		PreparedStatement stmt = null;
    		ResultSet rs = null;
    		try {
    			String query = "select idCliente,nombre,ApellidoMat,ApellidoPat,Email,Direccion,foto,Telf1,Telf2,nota from cliente where idCliente=?";
    			con = ConexionDB.obtenerConexion();
    			stmt = con.prepareStatement(query);
    			stmt.setString(1, idCliente);
    			rs = stmt.executeQuery();
    			if (rs.next()) {
    				vo.setIdCliente(rs.getString(1));
    				vo.setNombre(rs.getString(2));
    				vo.setApellidoMat(rs.getString(3));
    				vo.setApellidoPat(rs.getString(4));
    				vo.setEmail(rs.getString(5));
    				vo.setDireccion(rs.getString(6));
    				vo.setFoto(rs.getString(7));
    				vo.setTelf1(rs.getString(8));
    				vo.setTelf2(rs.getString(9));
    				vo.setNota(rs.getString(10));
    			}
    		} catch (SQLException e) {
    			System.err.println(e.getMessage());
    			throw new DAOExcepcion(e.getMessage());
    		} finally {
    			this.cerrarResultSet(rs);
    			this.cerrarStatement(stmt);
    			this.cerrarConexion(con);
    		}
    		return vo;
    	}
        
        public Collection<Cliente> budcarPorNombre(String nombre) throws DAOExcepcion {
        	System.out.println("ClienteDAO: listar(String nombre)");
    		String query = "SELECT idCliente,Nombre,ApellidoMat,ApellidoPat,email,direccion,foto,telf1,telf2,nota from cliente WHERE nombre like ?";
    		Collection<Cliente> c = new ArrayList<Cliente>();
    		Connection con = null;
    		PreparedStatement stmt = null;
    		ResultSet rs = null;
    		try {
    			con = ConexionDB.obtenerConexion();    			
    			stmt = con.prepareStatement(query);
    			stmt.setString(1, "%" + nombre + "%");
    			rs = stmt.executeQuery();
    			while (rs.next()) {
    				Cliente vo = new Cliente();
    				
    				vo.setIdCliente(rs.getString("idCliente"));    				
    				vo.setNombre(rs.getString("Nombre"));
    				vo.setApellidoMat(rs.getString("ApellidoMat"));
    				vo.setApellidoPat(rs.getString("ApellidoPat"));
    				vo.setEmail(rs.getString("email"));
    				vo.setDireccion(rs.getString("direccion"));
    				vo.setFoto(rs.getString("foto"));
    				vo.setTelf1(rs.getString("telf1"));
    				vo.setTelf2(rs.getString("telf2"));
    				vo.setNota(rs.getString("nota"));
    				c.add(vo);
    			}

    		} catch (SQLException e) {
    			System.err.println(e.getMessage());
    			throw new DAOExcepcion(e.getMessage());
    		} finally {
    			this.cerrarResultSet(rs);
    			this.cerrarStatement(stmt);
    			this.cerrarConexion(con);
    		}
    		return c;
    	}
        
        public Collection<Cliente> listar() throws DAOExcepcion {
    		System.out.println("ClienteDAO: listar()");
    		Collection<Cliente> c = new ArrayList<Cliente>();
    		Connection con = null;
    		PreparedStatement stmt = null;
    		ResultSet rs = null;
    		try {
    			con = ConexionDB.obtenerConexion();
    			String query = "SELECT idCliente,Nombre,ApellidoMat,ApellidoPat,email,direccion,foto,telf1,telf2,nota from cliente";
    			stmt = con.prepareStatement(query);
    			rs = stmt.executeQuery();
    			while (rs.next()) {
    				Cliente vo = new Cliente();
    				vo.setIdCliente(rs.getString("idCliente"));    				
    				vo.setNombre(rs.getString("Nombre"));
    				vo.setApellidoMat(rs.getString("ApellidoMat"));
    				vo.setApellidoPat(rs.getString("ApellidoPat"));
    				vo.setEmail(rs.getString("email"));
    				vo.setDireccion(rs.getString("direccion"));
    				vo.setFoto(rs.getString("foto"));
    				vo.setTelf1(rs.getString("telf1"));
    				vo.setTelf2(rs.getString("telf2"));
    				vo.setNota(rs.getString("nota"));
    				c.add(vo);
    			}
    		} catch (SQLException e) {
    			System.err.println(e.getMessage());
    			throw new DAOExcepcion(e.getMessage());
    		} finally {
    			this.cerrarStatement(stmt);
    			this.cerrarConexion(con);
    		}
    		return c;
    	}

        public Collection<Cliente> buscarPorIdCliente(String idCliente) throws DAOExcepcion {
        	
    		String query = "SELECT idCliente,Nombre,ApellidoMat,ApellidoPat,email,direccion,foto,telf1,telf2,nota from cliente WHERE idCliente like ?";
    		Collection<Cliente> c = new ArrayList<Cliente>();
    		Connection con = null;
    		PreparedStatement stmt = null;
    		ResultSet rs = null;
    		try {
    			con = ConexionDB.obtenerConexion();    			
    			stmt = con.prepareStatement(query);
    			stmt.setString(1, "%" + idCliente + "%");
    			rs = stmt.executeQuery();
    			while (rs.next()) {
    				Cliente vo = new Cliente();
    				
    				vo.setIdCliente(rs.getString("idCliente"));    				
    				vo.setNombre(rs.getString("Nombre"));
    				vo.setApellidoMat(rs.getString("ApellidoMat"));
    				vo.setApellidoPat(rs.getString("ApellidoPat"));
    				vo.setEmail(rs.getString("email"));
    				vo.setDireccion(rs.getString("direccion"));
    				vo.setFoto(rs.getString("foto"));
    				vo.setTelf1(rs.getString("telf1"));
    				vo.setTelf2(rs.getString("telf2"));
    				vo.setNota(rs.getString("nota"));
    				c.add(vo);
    			}

    		} catch (SQLException e) {
    			System.err.println(e.getMessage());
    			throw new DAOExcepcion(e.getMessage());
    		} finally {
    			this.cerrarResultSet(rs);
    			this.cerrarStatement(stmt);
    			this.cerrarConexion(con);
    		}
    		return c;
    	}

        
}